package com.gxuwz.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.menu.Menu;
import com.gxuwz.medical.tools.PageBean;
import com.gxuwz.medical.tools.StringUtil;

public class MenuDao {

	DbUtil dbUtil = new DbUtil(); // 数据库工具类

	/**
	 * 
	 * @Title: getAllMenu @Description: 查询权限树 @author 陈震洺 @return @throws
	 */
	public List<Menu> getAllMenu() {
		List<Menu> list = new ArrayList<Menu>(); // 创建集合

		String sql = "select * from menu"; // SQL查询语句
		try {
			Connection con = null;
			con = dbUtil.getCon();
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Menu menu = new Menu();
				menu.setId(rst.getString("id"));
				menu.setName(rst.getString("name"));
				menu.setPid(rst.getString("pid"));
				menu.setUrl(rst.getString("url"));
				list.add(menu);
			}
			rst.close(); // 关闭
			pst.close(); // 关闭
		} catch (SQLException e) {
			e.printStackTrace(); // 抛出异常
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list; // 返回一个集合
	}

	/**
	 * 
	 * @Title: findMenuById @Description: 根据ID查询菜单ID是否已经存在 @author 陈震洺 @param
	 * menu @return @throws
	 */
	public List<Menu> findMenuById(Menu menu) {
		List<Menu> list = new ArrayList<Menu>(); // 创建集合

		String sql = "select * from menu where id ='" + menu.getId() + "'"; // SQL查询语句
		try {
			Connection con = null;
			con = dbUtil.getCon();
			PreparedStatement pst = con.prepareStatement(sql);
			ResultSet rst = pst.executeQuery();
			while (rst.next()) {
				Menu menus = new Menu();
				menus.setId(rst.getString("id"));
				menus.setName(rst.getString("name"));
				menus.setPid(rst.getString("pid"));
				menus.setUrl(rst.getString("url"));
				list.add(menus);
			}
			rst.close(); // 关闭
			pst.close(); // 关闭
		} catch (SQLException e) {
			e.printStackTrace(); // 抛出异常
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list; // 返回一个集合
	}

	/**
	 * 
	 * @Title: MenuList @Description: 查询菜单信息 @author 陈震洺 @param con @param
	 *         pageBean @param menu @return @throws Exception @throws
	 */
	public ResultSet MenuList(Connection con, PageBean pageBean, Menu menu) throws Exception {

		StringBuffer sb = new StringBuffer("select * from menu where 1=1");
		System.out.println("menu=" + menu);
		// 下面为查询条件配值
		if (menu != null && !StringUtil.isEmpty(menu.getId())) {
			sb.append(" and id like '%" + menu.getId() + "%'");
		}

		if (menu != null && !StringUtil.isEmpty(menu.getName())) {
			sb.append(" and name like '%" + menu.getName() + "%'");
		}

		if (menu != null && !StringUtil.isEmpty(menu.getPid())) {
			sb.append(" and pid ='" + menu.getPid() + "'");
		}
		if (menu != null && !StringUtil.isEmpty(menu.getUrl())) {
			sb.append(" and name like '%" + menu.getUrl() + "%'");
		}

		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}

	/**
	 * 
	 * @Title: MenuCount @Description: 查询菜单数量 @author 陈震洺 @param con @param
	 *         menu @return @throws Exception @throws
	 */
	public int MenuCount(Connection con, Menu menu) throws Exception {
		StringBuffer sb = new StringBuffer("select count(*) as total from menu where 1=1");
		// 下面为查询条件配值
		if (!StringUtil.isEmpty(menu.getId())) {
			sb.append(" and id like '%" + menu.getId() + "%'");
		}

		if (!StringUtil.isEmpty(menu.getName())) {
			sb.append(" and name like '%" + menu.getName() + "%'");
		}

		if (!StringUtil.isEmpty(menu.getPid())) {
			sb.append(" and pid ='" + menu.getPid() + "'");
		}
		if (!StringUtil.isEmpty(menu.getUrl())) {
			sb.append(" and name like '%" + menu.getUrl() + "%'");
		}

		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");
		} else {
			return 0;
		}
	}

	/**
	 * 
	 * @Title: menuDelete @Description: 删除菜单 @author 陈震洺 @param con @param
	 *         delIds @return @throws Exception @throws
	 */
	public int menuDelete(Connection con, String delIds) throws Exception {
		String sql = "delete from menu where id in(" + delIds + ")";
		PreparedStatement pstmt = con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}

	/**
	 * 
	 * @Title: menuAdd @Description: 添加菜单 @author 陈震洺 @param con @param
	 *         menu @return @throws Exception @throws
	 */
	public int menuAdd(Connection con, Menu menu) throws Exception {
		String sql = "insert into menu values(?,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, menu.getId());
		pstmt.setString(2, menu.getName());
		pstmt.setString(3, menu.getPid());
		pstmt.setString(4, menu.getUrl());
		return pstmt.executeUpdate();
	}

	/**
	 * 
	 * @Title: menuModify @Description: 修改菜单 @author 陈震洺 @param con @param
	 *         menu @return @throws Exception @throws
	 */
	public int menuModify(Connection con, Menu menu) throws Exception {
		String sql = "update menu set name=?,pid=?,url=?where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		System.out.println("menu.getName()==" + menu.getName());
		pstmt.setString(1, menu.getName());
		pstmt.setString(2, menu.getPid());
		pstmt.setString(3, menu.getUrl());
		pstmt.setString(4, menu.getId());
		return pstmt.executeUpdate();
	}

}
